import os
import django
import openpyxl
import tablib
from django.conf import settings
from openpyxl.styles import Alignment

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'djexport.settings')
django.setup()
from django.test import TestCase
from aitest.resources import AITestCaseResource, AITestDetailResource, BuildInfoResource

# Create your tests here.
# dataset = AITestCaseResource().export()
# print(dataset.csv)
# print(type(dataset.csv))

# Create your tests here.
aitest_case_resource = AITestCaseResource()
aitest_detail_resource = AITestDetailResource()
build_info_resource = BuildInfoResource()

# Build info data
build_info_dataset = build_info_resource.export()

# Export data
aitest_case_dataset = aitest_case_resource.export()
heads1 = aitest_case_dataset.headers
values1 = aitest_case_dataset.dict
print(heads1)
print(values1)
aitest_detail_dataset = aitest_detail_resource.export()
heads2 = aitest_detail_dataset.headers
values2 = aitest_detail_dataset.dict
print(heads2)
print(values2)
total_heads = heads1 + heads2
print(total_heads)
total_values = []
for index, test in enumerate(values1):
    new_detail_list = []
    for detail in values2:
        if test['id'] == detail['ai_test_case']:
            new_detail_list.append(detail)
    # new_detail_list  排序，根据index的value值
    new_detail_list.sort(key=lambda x: x['index'])
    test['ai_test_detail'] = new_detail_list
    values1[index] = test
print(values1)

# Create a new Excel workbook and sheet
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "AITest Data"

# 将build_info数据写入Excel,生成一个新的sheet
ws_build_info = wb.create_sheet("Build Info")
for col_num, header in enumerate(build_info_dataset.headers, 1):
    cell = ws_build_info.cell(row=1, column=col_num, value=header)
    cell.border = openpyxl.styles.Border(
        left=openpyxl.styles.Side(style='thin'),
        right=openpyxl.styles.Side(style='thin'),
        top=openpyxl.styles.Side(style='thin'),
        bottom=openpyxl.styles.Side(style='thin')
    )
for row_num, row in enumerate(build_info_dataset.dict, 2):
    for col_num, key in enumerate(build_info_dataset.headers, 1):
        cell = ws_build_info.cell(row=row_num, column=col_num, value=row[key])
        cell.border = openpyxl.styles.Border(
            left=openpyxl.styles.Side(style='thin'),
            right=openpyxl.styles.Side(style='thin'),
            top=openpyxl.styles.Side(style='thin'),
            bottom=openpyxl.styles.Side(style='thin')
        )

# Write headers
for col_num, header in enumerate(total_heads, 1):
    cell = ws.cell(row=1, column=col_num, value=header)
    cell.border = openpyxl.styles.Border(
        left=openpyxl.styles.Side(style='thin'),
        right=openpyxl.styles.Side(style='thin'),
        top=openpyxl.styles.Side(style='thin'),
        bottom=openpyxl.styles.Side(style='thin')
    )

# Write data
row_num = 2
for test in values1:
    if test['ai_test_detail']:
        start_row = row_num
        for detail in test['ai_test_detail']:
            for col_num, key in enumerate(heads1, 1):
                cell = ws.cell(row=row_num, column=col_num, value=test[key])
                cell.border = openpyxl.styles.Border(
                    left=openpyxl.styles.Side(style='thin'),
                    right=openpyxl.styles.Side(style='thin'),
                    top=openpyxl.styles.Side(style='thin'),
                    bottom=openpyxl.styles.Side(style='thin')
                )
            for col_num, key in enumerate(heads2[1:], len(heads1) + 1):  # Skip the duplicate 'id'
                cell = ws.cell(row=row_num, column=col_num, value=detail[key])
                cell.border = openpyxl.styles.Border(
                    left=openpyxl.styles.Side(style='thin'),
                    right=openpyxl.styles.Side(style='thin'),
                    top=openpyxl.styles.Side(style='thin'),
                    bottom=openpyxl.styles.Side(style='thin')
                )
            row_num += 1
        end_row = row_num - 1
        for col_num in range(1, len(heads1) + 1):
            ws.merge_cells(start_row=start_row, start_column=col_num, end_row=end_row, end_column=col_num)
            cell = ws.cell(row=start_row, column=col_num)
            cell.alignment = Alignment(vertical='top')
            cell.border = openpyxl.styles.Border(
                left=openpyxl.styles.Side(style='thin'),
                right=openpyxl.styles.Side(style='thin'),
                top=openpyxl.styles.Side(style='thin'),
                bottom=openpyxl.styles.Side(style='thin')
            )
    else:
        for col_num, key in enumerate(heads1, 1):
            cell = ws.cell(row=row_num, column=col_num, value=test[key])
            cell.border = openpyxl.styles.Border(
                left=openpyxl.styles.Side(style='thin'),
                right=openpyxl.styles.Side(style='thin'),
                top=openpyxl.styles.Side(style='thin'),
                bottom=openpyxl.styles.Side(style='thin')
            )
        row_num += 1

# Save the Excel file
wb.save('aitest_data.xlsx')

# Create a new Excel file
# data = tablib.Databook((aitest_case_dataset, aitest_detail_dataset))
#
# # Save the Excel file
# with open('aitest_data.xlsx', 'wb') as f:
#     f.write(data.export('xlsx'))